package com.mapper;

import com.Dto.AllSupervisorInfoDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
@Mapper
public interface AllSupervisorInfoMapper {

    /**所有督导姓名，督导名下的咨询师姓名，援助总次数，援助总时长，本月排班日期
     *
     * @return
     */

    @Select("SELECT s.name, s.user_id AS supervisorId, GROUP_CONCAT(DISTINCT c.name) AS counselorBoundName,\n" +
            "       gs.totalSessionCount,\n" +
            "       SEC_TO_TIME(gs.totalSessionDurationSec) AS totalSessionDuration,\n" +
            "       GROUP_CONCAT(DISTINCT ss.date) AS date\n" +
            "FROM supervisor s\n" +
            "         LEFT JOIN supervisor_scheduling ss ON ss.supervisor_id = s.id AND\n" +
            "                                               MONTH(ss.date) = MONTH(CURRENT_DATE()) AND YEAR(ss.date) = YEAR(CURRENT_DATE())\n" +
            "         LEFT JOIN relationship r ON s.id = r.supervisor_id\n" +
            "         LEFT JOIN counselor c ON r.counselor_id = c.id\n" +
            "         LEFT JOIN (\n" +
            "             SELECT supervisor_id, SUM(TIME_TO_SEC(guidance_duration)) AS totalSessionDurationSec,\n" +
            "                    COUNT(guidance_session.id) AS totalSessionCount\n" +
            "                FROM guidance_session\n" +
            "                GROUP BY supervisor_id) gs ON gs.supervisor_id = s.id\n" +
            "GROUP BY s.id,gs.totalSessionCount, totalSessionDuration;")
    List<AllSupervisorInfoDto> findAllSupervisorInfo();

}
